package com.javasm.dao.MisMannageDao.impl;

import com.javasm.dao.MisMannageDao.AuditDao;
import com.javasm.entity.MisMannageEntity.ProdInfo;
import com.javasm.entity.MisMannageEntity.UserInfo;
import com.javasm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class AuditDaoImpl implements AuditDao {

    @Override
    public UserInfo getname(Integer userId) {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        UserInfo userInfo = new UserInfo();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT " +
                "user_name " +
                "from tb_user  " +
                "WHERE user_id = " + userId);
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                userInfo.setUserName(resultSet.getString("user_name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return userInfo;
    }

    @Override
    public Integer editStatus(String prodName, String auditStatus) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        Integer resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("update tb_prod_info set audit_status = '" + auditStatus +
                "' WHERE prod_name = '" + prodName + "'");
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeUpdate();
            System.out.println(resultSet);
            System.out.println(prodName);
            System.out.println(auditStatus);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return resultSet;
    }

    @Override
    public ProdInfo getProdByName(String prodName) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ProdInfo prodInfo = new ProdInfo();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT " +
                "ps.series_CHname , tt.type_name, pi.remit_company, " +
                "pi.rate_year, pi.money_type, pi.opening_time, pi.subscribe_cycle, pi.fund_manage_rate, " +
                "pi.subscribe_rate, pi.min_money, pi.money_submit_style, pi.redeem_cycle, " +
                "pi.redeem_min_money, pi.redeem_fee, pi.closed_cycle, pi.reviewer_id,pi.audit_status " +
                "from tb_prod_info pi ,tb_prod_series ps, tb_type tt ,tb_user uu " +
                "WHERE pi.series_id = ps.series_id " +
                "AND pi.second_type_id=tt.type_id " +
                "AND pi.prod_name ='" + prodName + "'");

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                prodInfo.setSeriesName(resultSet.getString("ps.series_CHname"));
                prodInfo.setSecondTypeName(resultSet.getString("tt.type_name"));
                prodInfo.setProdName(prodName);
                prodInfo.setRemitCompany(resultSet.getString("pi.remit_company"));
                prodInfo.setRateYear(resultSet.getString("pi.rate_year"));
                prodInfo.setMoneyType(resultSet.getString("pi.money_type"));
                prodInfo.setOpeningTime(resultSet.getDate("pi.opening_time"));
                prodInfo.setSubscribeCycle(resultSet.getInt("pi.subscribe_cycle"));
                prodInfo.setFundManageRate(resultSet.getString("pi.fund_manage_rate"));
                prodInfo.setSubscribeRate(resultSet.getString("pi.subscribe_rate"));
                prodInfo.setMinMoney(resultSet.getBigDecimal("pi.min_money"));
                prodInfo.setMoneySubmitStyle(resultSet.getString("pi.money_submit_style"));
                prodInfo.setRedeemCycle(resultSet.getInt("pi.redeem_cycle"));
                prodInfo.setRedeemMinMoney(resultSet.getBigDecimal("pi.redeem_min_money"));
                prodInfo.setRedeemFee(resultSet.getBigDecimal("pi.redeem_fee"));
                prodInfo.setClosedCycle(resultSet.getInt("pi.closed_cycle"));
                prodInfo.setReviewerId(resultSet.getInt("pi.reviewer_id"));
                prodInfo.setAuditStatus(resultSet.getString("pi.audit_status"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return prodInfo;
    }

    @Override
    public List<ProdInfo> getProd(Integer page, Integer pageSize, String prodName, String auditsStatus, Integer id) {

        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<ProdInfo> tbShowList = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("SELECT " +
                "pi.prod_name , " +
                "pi.prod_create_time , " +
                "pi.prod_update_time , " +
                "pi.reviewer_id , " +
                "pi.audit_status , " +
                "tt.type_name , " +
                "uu.user_name " +
                "from tb_prod_info pi ,tb_type tt ,tb_user uu " +
                "WHERE pi.second_type_id = tt.type_id AND pi.create_user_id=uu.user_id AND pi.reviewer_id = " + id);
        if (prodName != null && !"".equals(prodName)) {
            stringBuilder.append(" AND pi.prod_name LIKE '%" + prodName + "%'");
        }
        if (auditsStatus != null && !"".equals(auditsStatus)) {
            stringBuilder.append(" AND pi.audit_status ='" + auditsStatus + "'");
        }
        stringBuilder.append(" LIMIT " + (page - 1) * pageSize + "," + pageSize);
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ProdInfo prodInfo = new ProdInfo();
                prodInfo.setProdCreateTime(resultSet.getDate("pi.prod_create_time"));
                prodInfo.setProdUpdateTime(resultSet.getDate("pi.prod_update_time"));
                prodInfo.setProdName(resultSet.getString("pi.prod_name"));
                prodInfo.setAuditStatus(resultSet.getString("pi.audit_status"));
                prodInfo.setSecondTypeName(resultSet.getString("tt.type_name"));
                prodInfo.setCreateUserName(resultSet.getString("uu.user_name"));
                prodInfo.setReviewerId(resultSet.getInt("pi.reviewer_id"));

                tbShowList.add(prodInfo);

                //System.out.println(prodInfo);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return tbShowList;
    }

    @Override
    public Integer getTotalnum(String prodName, String auditsStatus, Integer id) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

//        System.out.println(prodName);
//        System.out.println(auditsStatus);
//        System.out.println(id);

        Integer totalnum = null;
        StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("SELECT count(1) totalnum from tb_prod_info pi where pi.reviewer_id = " + id);

        if (prodName != null && !"".equals(prodName)) {
            stringBuilder.append(" and ");
            stringBuilder.append(" pi.prod_name LIKE '%" + prodName + "%'");
        }
        if (auditsStatus != null && !"".equals(auditsStatus)) {
            stringBuilder.append(" and ");
            stringBuilder.append(" pi.audit_status ='" + auditsStatus + "'");
        }
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                totalnum = resultSet.getInt("totalnum");
            }
            //System.out.println(totalnum + "开始");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }


        return totalnum;
    }

}
